package com.zr.d_c.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zr.d_c.dao.BookDao;
import com.zr.d_c.model.Dish;
import com.zr.d_c.model.Seller;
import com.zr.d_c.util.JDBCUtil;

/**
 * 实现预定dao
 * @author 王朝
 *
 */
public class BookDaoImpl implements BookDao{

	@Override
	public int selectSellerCount() {
		// TODO 查询商家数量
		int count = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(*) from d_cseller");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	@Override
	public int selectSellerCount(String sname) {
		// TODO 模糊查询商家数量
		int count = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(*) from d_cseller where sname like ?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, "%"+sname+"%");
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	@Override
	public List<Seller> selectSeller(int start,int pagesize) {
		// TODO 查询商家信息
		List<Seller> ls = new ArrayList<Seller>();
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select * from d_cseller limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, start);
			pst.setInt(2, pagesize);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				Seller s = new Seller();
				s.setSid(rs.getInt("sid"));
				s.setSname(rs.getString("sname"));
				s.setSphone(rs.getString("sphone"));
				s.setSaddress(rs.getString("saddress"));
				s.setSemail(rs.getString("semail"));
				ls.add(s);
			}
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ls;
	}

	@Override
	public List<Seller> selectSeller(int start,int pagesize,String sname) {
		// TODO 模糊查询商家信息
		List<Seller> ls = new ArrayList<Seller>();
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select * from d_cseller where sname like ? limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, "%"+sname+"%");
			pst.setInt(2, start);
			pst.setInt(3, pagesize);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				Seller s = new Seller();
				s.setSid(rs.getInt("sid"));
				s.setSname(rs.getString("sname"));
				s.setSphone(rs.getString("sphone"));
				s.setSaddress(rs.getString("saddress"));
				s.setSemail(rs.getString("semail"));
				ls.add(s);
			}
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ls;
	}

	@Override
	public int selectDishCount(int sid) {
		// TODO 查询菜品数量
		int count = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(*) from d_cdish where sid=?");
		sql.append(" and disdelete='1'");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, sid);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	@Override
	public int selectDishCount(int sid,String dname) {
		// TODO 模糊查询菜品数量
		int count = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(*) from d_cdish where sid=?");
		sql.append(" and disdelete='1'");
		sql.append(" and dname like ?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, sid);
			pst.setString(2, "%"+dname+"%");
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	@Override
	public List<Dish> selectDish(int start, int pagesize,int sid) {
		// TODO 查询菜品信息
		List<Dish> ld = new ArrayList<Dish>();
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select * from d_cdish where sid=?");
		sql.append(" and disdelete='1' limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, sid);
			pst.setInt(2, start);
			pst.setInt(3, pagesize);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				Dish d = new Dish();
				d.setDid(rs.getInt("did"));
				d.setDname(rs.getString("dname"));
				d.setDprice(rs.getInt("dprice"));
				d.setDnum(rs.getInt("dnum"));
				d.setDtemp1(rs.getString("dtemp1"));
				ld.add(d);
			}JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ld;
	}

	@Override
	public List<Dish> selectDish(int start, int pagesize,int sid, String dname) {
		// TODO 模糊查询菜品信息
		List<Dish> ld = new ArrayList<Dish>();
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select * from d_cdish where sid=?");
		sql.append(" and disdelete='1' and dname like ? limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, sid);
			pst.setString(2, "%"+dname+"%");
			pst.setInt(3, start);
			pst.setInt(4, pagesize);
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				Dish d = new Dish();
				d.setDid(rs.getInt("did"));
				d.setDname(rs.getString("dname"));
				d.setDprice(rs.getInt("dprice"));
				d.setDnum(rs.getInt("dnum"));
				d.setDtemp1(rs.getString("dtemp1"));
				ld.add(d);
			}JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ld;
	}

	@Override
	public int insertDishToBuycart(int did, int cid, int bnum) {
		// TODO 插入到购物车
		int flag = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("insert into d_cbuycart (did,cid,bnum) value(?,?,?)");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, did);
			pst.setInt(2, cid);
			pst.setInt(3, bnum);
			int i = pst.executeUpdate();
			if (i>0) {
				flag = 1;
			}
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public int selectCidByUid(int uid) {
		// TODO 查询当前用户的顾客id
		int i = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select cid from d_ccustomer where uid=?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, uid);
			ResultSet rs = pst.executeQuery();
			if (rs.next()) {
				i = rs.getInt("cid");
			}
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}

	@Override
	public int selectBuycartDishByDid(int did) {
		// TODO 查询购物车中已存在的菜
		int flag = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select bnum from d_cbuycart where did=?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, did);
			ResultSet rs = pst.executeQuery();
			if (rs.next()) {
				flag = rs.getInt("bnum");
			}
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public int updateBuycartDishByDid(int did, int totalbnum) {
		// TODO 更新购物车中已存在的菜
		int flag = 0;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("update d_cbuycart set bnum=? where did=?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, totalbnum);
			pst.setInt(2, did);
			int i = pst.executeUpdate();
			if (i>0) {
				flag = 1;
			}
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}

}
